﻿Public Class Factura
    Dim conexion_bd As String
    Enum grabado
        ok
        errores
    End Enum
    Enum estado
        insertar
        modificar
    End Enum
    Enum termino
        aprobado
        rechazado
    End Enum
    Dim accion As estado = estado.modificar
    Dim grabar As grabado = grabado.errores

    Private Sub Factura_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'conexión a la base de datos'
        Me.conexion_bd = "Provider=SQLOLEDB;Data Source=GTºN1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=C:\DB\LIBRERIA_MAGOO.MDF"

        'carga los combos
        carga_combo(Me.cmb_pago, leo_tabla("MedioPago"), "id_pago", "descripcion")
        Me.habilitar(False)
        Me.btn_nuevo.Focus()
    End Sub
    Private Sub txt_cantidad_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_cantidad.TextChanged
        Dim a, b As Double
        a = Val(Me.txt_precio.Text.Replace(",", "."))
        b = Val(Me.txt_cantidad.Text.Replace(",", "."))

        Me.txt_totalarticulo.Text = a * b
    End Sub

    Private Sub btn_buscarcli_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscarcli.Click
        If Me.txt_dni.Text = "" Or (Not (IsNumeric(Me.txt_dni.Text))) Then
            MessageBox.Show("Número de cliente incorrecto o incompleto", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Me.buscar_clientes()
        End If
    End Sub

    Private Sub btn_guardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_guardar.Click
        If Me.validar() = True Then
            If Me.accion = estado.insertar Then
                If Me.insertar_venta = grabado.errores Then
                    MessageBox.Show("Error al insertar venta ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    If Me.insertar_detalle = grabado.errores Then
                        MessageBox.Show("Error al insertar detalle de venta ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        MessageBox.Show("Venta ingresada correctamente", "Factura Nro: " & Me.txt_numero.Text & "", MessageBoxButtons.OK, MessageBoxIcon.Information)

                        habilitar(False)
                    End If
                End If
            End If
        End If

    End Sub
    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        Me.habilitar(True)
        Me.dtp_fecha.Focus()
        Me.accion = estado.insertar
    End Sub
    Private Sub btn_quitar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_quitar.Click
        If (grilla.Rows.Count > 0 And (Not (IsNothing(grilla.CurrentRow)))) Then
                txt_total.Text -= grilla.CurrentRow.Cells(4).Value
                txt_subtotal.Text -= grilla.CurrentRow.Cells(4).Value
                grilla.Rows.Remove(grilla.CurrentRow)
        Else
            MessageBox.Show("No ha seleccionado un libro para quitar de la grilla", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub
    Private Sub btn_agregar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_agregar.Click
        Dim ban As Integer = 0

        If Me.txt_cantidad.Text = "" Or (Not (IsNumeric(Me.txt_cantidad.Text))) Then
            MessageBox.Show("Cantidad incorrecta o incompleta", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_cantidad.Focus()
        Else
            For i As Integer = 0 To grilla.Rows.Count - 1
                If grilla.Rows(i).Cells(2).Value = txt_isbn.Text Then
                    ban = 1
                    MessageBox.Show("El libro seleccionado ya fue ingresado", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit For
                End If
            Next

            If ban = 0 Then
                Me.grilla.Rows.Add(Me.txt_cantidad.Text, Me.txt_titulo.Text, Me.txt_isbn.Text, Me.txt_precio.Text, Me.txt_totalarticulo.Text)

                Me.txt_titulo.Text = ""
                Me.txt_cantidad.Text = ""
                Me.txt_precio.Text = ""
                Me.txt_isbn.Text = ""
                Me.txt_totalarticulo.Text = ""
                Me.txt_subtotal.Text = Me.calcular()
                Me.txt_total.Text = Me.txt_subtotal.Text

                ban = 0

            End If

        End If

    End Sub
    Private Sub btn_buscarart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscarart.Click
        If Me.txt_isbn.Text = "" Or (Not (IsNumeric(Me.txt_isbn.Text))) Then
            MessageBox.Show("Número ISBN incorrecto o vacío", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Me.buscar_articulo()
        End If
    End Sub

    Private Sub carga_combo(ByRef combo As ComboBox, ByRef tabla As Data.DataTable, ByVal pk As String, ByVal nombre As String)
        combo.DataSource = tabla
        combo.ValueMember = pk
        combo.DisplayMember = nombre
        combo.SelectedIndex = -1
    End Sub
    Private Sub habilitar(ByVal x As Boolean)
        Me.cmb_pago.Enabled = x
        Me.txt_cantidad.Enabled = x
        Me.txt_cliente.Enabled = x
        Me.txt_idcliente.Enabled = x
        Me.txt_dni.Enabled = x
        Me.dtp_fecha.Enabled = x
        Me.txt_isbn.Enabled = x
        Me.txt_numero.Enabled = x
        Me.txt_precio.Enabled = x
        Me.txt_subtotal.Enabled = x
        Me.txt_descuento.Enabled = x
        Me.txt_titulo.Enabled = x
        Me.txt_total.Enabled = x
        Me.txt_totalarticulo.Enabled = x
        Me.btn_nuevo.Enabled = Not x
        Me.btn_buscarart.Enabled = x
        Me.btn_buscarcli.Enabled = x
        Me.btn_agregar.Enabled = x
        Me.btn_guardar.Enabled = x
        Me.btn_quitar.Enabled = x

        Me.cmb_pago.SelectedIndex = -1
        Me.txt_cantidad.Text = ""
        Me.txt_cliente.Text = ""
        Me.txt_dni.Text = ""
        Me.txt_idcliente.Text = ""
        Me.dtp_fecha.Text = ""
        Me.txt_isbn.Text = ""
        Me.txt_numero.Text = ""
        Me.txt_precio.Text = ""
        Me.txt_subtotal.Text = ""
        Me.txt_descuento.Text = ""
        Me.txt_titulo.Text = ""
        Me.txt_total.Text = ""
        Me.txt_totalarticulo.Text = ""
        Me.txt_nroTarjeta.Text = ""

        Me.grilla.Rows.Clear()
    End Sub
    Private Sub buscar_clientes()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select apellido+', '+nombre as nombres, id_cliente from Cliente where num_doc= '" & Me.txt_dni.Text & "' "
        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        If IsNumeric(Me.txt_dni.Text) = False Then
            MessageBox.Show("Número de documento inválido", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_dni.Focus()
        Else
            If tabla.Rows.Count > 0 Then 'me fijo si se recuperó algo
                Me.txt_cliente.Text = tabla.Rows(0)("nombres")
                Me.txt_idcliente.Text = tabla.Rows(0)("id_cliente")
            Else
                MessageBox.Show("El Cliente no existe", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Me.txt_dni.Focus()
            End If
        End If
        conexion.Close()
    End Sub
    Private Sub buscar_articulo()
            Dim conexion As New Data.OleDb.OleDbConnection
            Dim command As New Data.OleDb.OleDbCommand
            Dim tabla As New Data.DataTable
            Dim consulta As String = ""

            conexion.ConnectionString = Me.conexion_bd
            conexion.Open()

            consulta = "select titulo, precio from Libro where isbn= '" & Me.txt_isbn.Text & "' "
            command.CommandType = CommandType.Text
            command.CommandText = consulta
            command.Connection = conexion

            tabla.Load(command.ExecuteReader())

            If tabla.Rows.Count > 0 Then 'me fijo si se recuperó algo
                Me.txt_titulo.Text = tabla.Rows(0)("titulo")
                Me.txt_precio.Text = tabla.Rows(0)("precio")
            Else
                MessageBox.Show("El Artículo no existe", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Me.txt_dni.Focus()
            End If

            conexion.Close()
    End Sub

    Private Function leo_tabla(ByVal nombre_tabla As String) As Data.DataTable
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select * from " & nombre_tabla

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        conexion.Close()
        Return tabla

    End Function

    Private Function validar() As Boolean
        If Me.dtp_fecha.Text = "" Then
            MessageBox.Show("La fecha está vacía", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.dtp_fecha.Focus()
            Return False
        End If

        If Me.txt_dni.Text = "" Then
            MessageBox.Show("El documento está vacío", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_dni.Focus()
            Return False
        End If

        If Me.txt_cliente.Text = "" Then
            MessageBox.Show("No se epecificó el Cliente", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_cliente.Focus()
            Return False
        End If

        If Me.cmb_pago.SelectedIndex = -1 Then
            MessageBox.Show("No seleccionó medio de pago", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.cmb_pago.Focus()
            Return False
        End If

        If Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Debito" Or Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Tarjeta" Then
            If Me.txt_nroTarjeta.Text = "" Or (Not (IsNumeric(Me.txt_nroTarjeta.Text))) Then
                MessageBox.Show("No ingresó un número de tarjeta válido", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
                Me.txt_nroTarjeta.Focus()
                Return False
            End If
        End If

        If Me.grilla.Rows.Count = 0 Then
            MessageBox.Show("Debe agregar al menos un libro para la venta", "Mensaje de error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Return False
        End If

        Return True

    End Function

    Private Function insertar_venta() As grabado
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = "" 'declaro la variable consulta, vacia'
        Dim tabla As New Data.DataTable


        'abro conexion'
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        If Me.txt_descuento.Text <> "" And Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) <> "Efectivo" Then
            consulta = "insert into Venta (fecha, id_pago, id_cliente, monto, descuento, num_tarjeta) values('" & Me.dtp_fecha.Text & "'," & Me.cmb_pago.SelectedValue & "," & Me.txt_idcliente.Text & "," & Me.txt_total.Text.Replace(",", ".") & "," & Me.txt_descuento.Text & ", " & txt_nroTarjeta.Text & ")"
        ElseIf Me.txt_descuento.Text = "" And Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) <> "Efectivo" Then
            consulta = "insert into Venta (fecha, id_pago, id_cliente, monto, num_tarjeta) values('" & Me.dtp_fecha.Text & "'," & Me.cmb_pago.SelectedValue & "," & Me.txt_idcliente.Text & ", " & Me.txt_total.Text.Replace(",", ".") & ", " & txt_nroTarjeta.Text & ")"
        ElseIf Me.txt_descuento.Text <> "" And Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Efectivo" Then
            consulta = "insert into Venta (fecha, id_pago, id_cliente, monto, descuento) values('" & Me.dtp_fecha.Text & "'," & Me.cmb_pago.SelectedValue & "," & Me.txt_idcliente.Text & ", " & Me.txt_total.Text.Replace(",", ".") & ", " & txt_descuento.Text & ")"
        ElseIf Me.txt_descuento.Text = "" And Me.cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Efectivo" Then
            consulta = "insert into Venta (fecha, id_pago, id_cliente, monto) values('" & Me.dtp_fecha.Text & "'," & Me.cmb_pago.SelectedValue & "," & Me.txt_idcliente.Text & ", " & Me.txt_total.Text.Replace(",", ".") & ")"
        End If

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion
        command.ExecuteNonQuery()

        conexion.Close()
        Me.tomar_num()
        Return grabado.ok
    End Function
    Private Function insertar_detalle() As grabado
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = "" 'declaro la variable consulta, vacia
        Dim i As Integer
        i = 0

        'abro conexion
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        'consulta de nuevo
        For i = 0 To Me.grilla.RowCount() - 1
            consulta = "insert into DetalleVenta values(" & Me.txt_numero.Text & "," & Me.grilla.Rows(i).Cells("ISBN").Value & "," & Me.grilla.Rows(i).Cells("cantidad").Value & "," & Me.grilla.Rows(i).Cells("PrecioUnitario").Value.ToString.Replace(",", ".") & ")"
            command.CommandType = CommandType.Text
            command.CommandText = consulta
            command.Connection = conexion
            command.ExecuteNonQuery()
            consulta = ""
        Next

        conexion.Close()
        Return grabado.ok
    End Function

    Private Function tomar_num()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select * from Venta"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())
        Me.txt_numero.Text = tabla.Rows(tabla.Rows.Count - 1)("num_factura")

        conexion.Close()
        Return tabla
    End Function

    Private Function calcular() As Double
        Dim i As Integer
        Dim acumulador As Double
        acumulador = 0

        For i = 0 To grilla.Rows.Count - 1
            acumulador = acumulador + Convert.ToDouble(Me.grilla.Rows(i).Cells(4).Value)
        Next
        Return acumulador

    End Function

    Private Sub txt_descuento_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_descuento.TextChanged
        If Me.txt_descuento.Text = "" Then
            Me.txt_total.Text = Me.txt_subtotal.Text
        ElseIf (IsNumeric(Me.txt_descuento.Text)) And Me.txt_subtotal.Text <> "" Then
            Me.txt_total.Text = Me.txt_subtotal.Text - (Me.txt_subtotal.Text * Me.txt_descuento.Text) / 100
        End If
    End Sub




    Private Sub cmb_pago_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_pago.SelectedIndexChanged
        If (Not (IsNothing(cmb_pago.SelectedItem))) Then
            If cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Debito" Or cmb_pago.GetItemText(cmb_pago.SelectedItem) = "Tarjeta" Then
                txt_nroTarjeta.Enabled = True
            Else
                txt_nroTarjeta.Enabled = False
            End If
        End If
    End Sub

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        If MessageBox.Show("Cerrar esta ventana?", "Importante", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) Then
            Me.Close()
        End If
    End Sub
End Class